#-*-coding:utf-8-*-
"""
数据处理类，用来读取所需要的数据
"""
import os
import sys
import time
import json
import copy
import datetime
import psycopg2
from mz_date import MZ_Date

class DB_handler(object):

    def __init__(self):
        super(DB_handler,self).__init__()

        # self.cgtw_connect = psycopg2.connect(database='taskprogress', user='timeaxis', password='timeaxis',
        #                                 host='192.168.1.222',port=5432)
        #
        # self.cgtw_cur =self.cgtw_connect.cursor()

        # self.time_connect=psycopg2.connect(database='postgres', user='timeaxis', password='timeaxis',
        #                                 host='192.168.1.222',port=5432)
        # self.time_cur=self.time_connect.cursor()
        self.__db_projectname_map={} #{'proj_gjwd': ['古剑网大', 'GJWD'], 'proj_xmm': ['致我们甜甜的小美满_BJ', 'XMM'],
        self.mz_data=MZ_Date()
        self._departments=[]
        self.departments=['01美术','02模型','03跟踪','04动画','05场景','06灯光','07特效一','07特效二','08合成一','08合成二',
        '09包装','10剪辑','11调色','12TD','13虚拟']

        self._department_artists_map={}  #{'01美术':[xx,xx,..],'02模型':[xx,xx,..],...}


    def _init_timeaxis_db(self):
        try:
            time_connect = psycopg2.connect(database='postgres',
                                          user='timeaxis',
                                          password='timeaxis',
                                          host='192.168.1.222',
                                          port=5432)
            time_cursor = time_connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:',cgdb_err)
            return None, None
        return time_connect, time_cursor

    def _init_taskprogress_db(self):
        """
        创建一个操作本地_init_taskprogress_db数据库的临时连接
        :return:
        """
        try:
            time_connect = psycopg2.connect(database='taskprogress',
                                          user='timeaxis',
                                          password='timeaxis',
                                          host='192.168.1.222',
                                          port=5432)
            time_cursor = time_connect.cursor()
        except Exception as _init_taskprogress_db_ERR:
            print('_init_taskprogress_db_ERR:',str(_init_taskprogress_db_ERR))
            return None, None
        return time_connect, time_cursor


    @property
    def project_map(self):
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select database,entity_name,full_name from timeaxis.r_project"
        db_projectname_map={'noproject':['非制作项目','NON']}  #{database:["无心法师","WXFS"],...}
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                # print(result)
                for i in result:
                    database,entity_name,full_name=i
                    db_projectname_map[database]=[full_name,entity_name]
        except Exception as get_r_project_ERR:
            print('get_r_project_ERR:',str(get_r_project_ERR))

        finally:
            time_connect.close()
        self.__db_projectname_map=db_projectname_map
        return db_projectname_map


    def department_list(self):
        """
        获取显示的部门列表
        :return:
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select name from timeaxis.r_department where display =1"
        d_list=[]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                # print(result)
                for i in result:
                    d_list.append(i[0])
        except Exception as get_r_project_ERR:
            print('get_r_project_ERR:',str(get_r_project_ERR))

        finally:
            time_connect.close()
        d_list = sorted(d_list)
        self._departments=d_list
        return d_list

    @property
    def department_artists_map(self):
        """
        获取显示部门中的所有艺术家
        :return: department_artists_map={}  #{'01美术':[xxx,xx],'02美术':[xxx,xx,..],...}
        """
        department_artists_map={}  #{'01美术':[xxx,xx],'02美术':[xxx,xx,..],...}
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select cg_id,name from timeaxis.r_department where display =1"

        department_id_name_map={}  #{'id':'01美术','id':'02...',..}

        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                # print(result)
                for i in result:
                    cg_id,name=i
                    department_id_name_map[cg_id]=name
            if department_id_name_map:
                for k,v in department_id_name_map.items():
                    a_sql="select artist from timeaxis.r_account where conf_department_id ='{}'".format(k)
                    time_cursor.execute(a_sql)
                    a_result = time_cursor.fetchall()
                    per_department_artists=[]
                    if a_result:
                        for a in a_result:
                            artist=a[0]
                            per_department_artists.append(artist)

                    department_artists_map[v]=per_department_artists
        except Exception as get_department_artists_map_ERR:
            print('get_department_artists_map_ERR:',str(get_department_artists_map_ERR))
        finally:
            time_connect.close()

        self._department_artists_map=department_artists_map
        return department_artists_map



    def select_task_in_dates(self,s_start_date,s_end_date):
        """
        提取指定时间段内的任务，获取有效的所有任务，排除掉show_workhour为0的任务
        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回有[{'task_type': 'cgtw', 'artist': '李静平', 'database': 'proj_spl', 'department': '05场景',
        'show_name': 'EP08_S32_C1030-env', 'show_workhour': '2', 'date_length': 1, 'start_date': '2020-11-21',
        'end_date': '2020-11-21', 'count_day': 1, 'count_workhour': 2.0},...}
        """
        all_task_info_list=[]

        select_cgtw="select artist,database,department,show_name,show_workhour,start_date,end_date,date_length from timeaxis.task where project_name is not null " \
                    "and project_name !='' and show_workhour is not null and show_workhour!='' and start_date <='{}' and end_date >='{}' " \
                    "and cast(show_workhour as float)>0 ".format(s_end_date,s_start_date)

        # select_custom="select artist,database,department,show_name,show_workhour,start_date,end_date,date_length from timeaxis.custom_task where project_name " \
        #               "is not null and project_name !='' and show_workhour is not null and show_workhour!='' and  start_date <='{}' and end_date >='{}' " \
        #             "and cast(show_workhour as float)>0 ".format(s_end_date,s_start_date)

        select_custom="select artist,database,department,show_name,show_workhour,start_date,end_date,date_length from timeaxis.custom_task where  " \
                      "show_workhour is not null and show_workhour!='' and  start_date <='{}' and end_date >='{}' " \
                    "and cast(show_workhour as float)>0 ".format(s_end_date,s_start_date)  #自定义任务 项目名称为空都算 非制作项目

        time_connect, time_cursor=self._init_timeaxis_db()
        try:
            #获取cgtw 任务
            time_cursor.execute(select_cgtw)
            result= time_cursor.fetchall()
            if result:
                print('cgtw任务有:',len(result))
                # print(result[0])
                over_task=[]
                for i in result:
                    artist,database,department,show_name,show_workhour,start_date,end_date,date_length=i
                    data={}
                    data['task_type']='cgtw'
                    data['artist']=artist
                    data['database']=database
                    data['department']=department
                    data['show_name']=show_name
                    data['show_workhour']=show_workhour
                    data['date_length']=date_length
                    data['start_date']=start_date
                    data['end_date']=end_date

                    data['count_day']=date_length  #count_day用来计算切分日期后工时
                    data['cut_start_date'] =start_date  #默认截断后的起始日期为 start_date
                    data['cut_end_date']=end_date
                    s_deltel_day= self.delta_date(s_start_date,start_date)#跨日期
                    e_deltel_day = self.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day<0:#当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day']+=s_deltel_day
                        over_task.append(data)
                        data['cut_start_date']=s_start_date #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day'] += e_deltel_day
                        over_task.append(data)
                        data['cut_end_date'] = s_end_date  #

                    data['count_workhour']=(float(show_workhour)/date_length) *data['count_day']  #被统计进来的工时

                    all_task_info_list.append(data)
                # print('cgtw over:task', len(over_task),over_task)

            #获取自定义任务
            time_cursor.execute(select_custom)
            custom_result = time_cursor.fetchall()
            if custom_result:
                print('custom任务有:',len(custom_result))
                # print(custom_result[0])
                for c in custom_result:
                    # artist,database, department, project_name,show_workhour,start_date,end_date,date_length = i
                    artist, database, department, show_name, show_workhour, start_date, end_date, date_length=c
                    c_data={}
                    c_data['task_type']='custom'
                    c_data['artist']=artist

                    c_data['database']=database if database and database!='' else 'noproject'
                    c_data['department']=department
                    c_data['show_name']=show_name
                    c_data['show_workhour']=show_workhour
                    c_data['date_length']=date_length
                    c_data['start_date']=start_date
                    c_data['end_date']=end_date
                    c_data['count_day']=date_length  #count_day用来计算切分日期后工时
                    c_data['cut_start_date'] =start_date  #默认截断后的起始日期为 start_date
                    c_data['cut_end_date']=end_date

                    s_deltel_day= self.delta_date(s_start_date,start_date)#跨日期
                    e_deltel_day = self.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day<0:#当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day']+=s_deltel_day
                        over_task.append(c_data)
                        data['cut_start_date']=s_start_date #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day'] += e_deltel_day
                        over_task.append(c_data)
                        c_data['cut_end_date'] = s_end_date  #

                    c_data['count_workhour']=(float(show_workhour)/date_length) *c_data['count_day']  #被统计进来的工时
                    # if artist == '张洲':
                    #     print('这里！！！', show_name)
                    #     print(c_data)
                    all_task_info_list.append(c_data)
        except Exception as select_task_in_dates_ERR:
            print(str(select_task_in_dates_ERR))
        finally:
            time_connect.close()

        return all_task_info_list

    def get_all_department_per_project_map(self,all_task_info_list):
        """
        传入获取到所有任务信息，根据部门来划分各项目的任务信息
        :param all_task_info_list:
        :return:{'01美术':{'proj_ylm':[task1,task2,...],'02模型':{'proj_ylm':[task1,task2,task3...],...}}
        最后通过database映射项目名称
        """
        all_department_per_project_map={}
        department_tasks={}
        # copy_all_task_info_list=copy.deepcopy(all_task_info_list)
        if all_task_info_list:
            for task in all_task_info_list:
                department=task['department']
                if department not in department_tasks.keys():
                    department_tasks[department]=[task]
                else:
                    department_tasks[department].append(task)

            for dep,ts in department_tasks.items():
                db_task={}
                for t in ts:
                    db=t['database']
                    if db not in db_task.keys():
                        db_task[db]=[t]
                    else:
                        db_task[db].append(t)
                all_department_per_project_map[dep]=db_task
        return all_department_per_project_map

    def get_sankey_data(self,all_department_per_project_map):
        """
        获取sankey数据：
        node:[{'name':'部门1'},{'name':'部门2'},{'name':'部门3'},...{'name':'项目1'},{'name':'项目2'},...]
        linkes:[{'source':'部门1','target':'项目2','value':20}, {'source':'部门1','target':'项目1','value':100},...]
        :param all_department_per_project_map:{'01美术':{'proj_ylm':[task1,task2,...],'02模型':{'proj_ylm':[task1,task2,task3...],...}}
        :return:
        """
        all_nodes=[]
        workhour_linkes=[]
        artist_linkes=[]

        if all_department_per_project_map:
            all_show_project_name=[]
            all_show_project_nodes=[]
            department_project_all_workhour={} #{'部门1':[('项目1',150)],''部门2':['项目1',150]'}
            for dep,proj_data in all_department_per_project_map.items():

                node={'name':dep}
                all_nodes.append(node)

                for database,task_list in proj_data.items(): #获取该部门下 所有项目以及项目对应总工时
                    workhour_source_data = {}  #
                    artist_source_data = {}  #
                    full_name, entity_name = self.project_map.get(database, ['非制作项目', 'NON'])
                    show_project_name = '{}({})'.format(full_name, entity_name)
                    if show_project_name not in all_show_project_name:
                        all_show_project_name.append(show_project_name)
                        all_show_project_nodes.append({'name':show_project_name})
                    per_project_all_workhour=0
                    per_project_all_artists = []  # 该项目下所有任务中出现的艺术家
                    for task in task_list:
                        per_project_all_workhour+=task['count_workhour']
                        artist=task['artist']
                        if artist not in per_project_all_artists:
                            per_project_all_artists.append(artist)
                    workhour_source_data['source']=dep
                    workhour_source_data['target']=show_project_name
                    workhour_source_data['value']=round(per_project_all_workhour,2)
                    workhour_linkes.append(workhour_source_data)
                    artist_source_data['source']=dep
                    artist_source_data['target']=show_project_name
                    artist_source_data['value']=len(per_project_all_artists)
                    artist_linkes.append(artist_source_data)

            all_nodes.extend(all_show_project_nodes)

        return all_nodes,workhour_linkes,artist_linkes

    def get_pie_data(self,all_task_info_list):
        """
        获取项目、人力饼图数据
        :param all_task_info_list:
        :return:project_name_workhour_map: {'皓衣行(HYX)': 311.5, '涉过愤怒的海(FNDH)': 780.0, '李焕英(NHL)': 30.0}
                artist_count_map: {'皓衣行(HYX)': 8, '涉过愤怒的海(FNDH)': 10, '李焕英(NHL)': 3}
        """
        # project_workhour_map={}
        project_name_workhour_map={}
        artist_map={}
        project_map=self.project_map
        for task in all_task_info_list:
            database=task['database']
            count_workhour=task['count_workhour']
            artist=task['artist']
            full_name,entity_name= project_map.get(database,['非制作项目','NON'])
            project_name='{}({})'.format(full_name,entity_name)
            if project_name not in project_name_workhour_map.keys():
                project_name_workhour_map[project_name]=count_workhour
            else:
                project_name_workhour_map[project_name]+= count_workhour

            if project_name not in artist_map.keys():
                artist_map[project_name]=set(artist)
            else:
                artist_map[project_name].add(artist)
        project_name_workhour_map=dict([k,round(v,2)] for k ,v in project_name_workhour_map.items())
        # print(artist_map)
        artist_count_map=dict([k,len(v)] for k ,v in artist_map.items())

        return project_name_workhour_map,artist_count_map


    def get_department_all_workhour_artists(self,all_department_per_project_map):
        """
        传入每部门，每项目任务列表 字典。计算每部门的安排的总工时，以及每个部门安排的人员
        :param all_department_per_project_map: {'01美术':{'proj_ylm':[task1,task2,...],'02模型':{'proj_ylm':[task1,task2,task3...],...}}
        :return: all_department_all_workhour : {'01美术':总工时,'02模型':总工时,...}

        """
        all_department_all_workhour={}
        all_department_use_artists={}
        if all_department_per_project_map:
            for department,project_data in all_department_per_project_map.items():
                per_department_all_workhour=0
                per_department_artist_set=set()
                for project,task_list in project_data.items():
                    per_project_all_workhour=0
                    for task in task_list:
                        count_workhour=task['count_workhour']
                        per_project_all_workhour+=count_workhour
                        artist=task['artist']
                        per_department_artist_set.add(artist)
                    per_department_all_workhour+=per_project_all_workhour
                all_department_all_workhour[department]=per_department_all_workhour
                all_department_use_artists[department]=per_department_artist_set
        return all_department_all_workhour,all_department_use_artists




    #通用计算类
    def data_divide_data(self,data,standard_data):
        """
        求出数据2在数据1中的比值，精确到小数点4位置
        :param data:{’01美术‘:8,'02跟踪':1,...}  #统计分配的各部门人数
        :param standard_data: {’01美术‘:8,'02跟踪':3,...} #每个部门的所有人数
        :return: {’01美术‘:1,'02跟踪':0.3333,...}
        """
        data_divide={}
        for k1,v1 in standard_data.items():
            v2=data.get(k1,0)
            d=round(v2/v1,4) if v1 else 0
            data_divide[k1]=d

        return data_divide

    def data_divide_const(self,data,const):
        """
        求出 data中数值与常熟const的比值。
        :param data:
        :param const:
        :return:
        """
        data_divide={}
        for k,v in data.items():
            artist_count=len(self._department_artists_map[k])
            print('data_divide_const:',v,k,artist_count)
            d = round(v /(const*artist_count), 4) if const else 0
            data_divide[k] = d
        print(data_divide)
        return data_divide

    def count_dict_value(self,dic):
        """
        输出字典，该字典的value一般是list或者set,返回他的值
        :param dic:
        :return:
        """
        new_dic={}
        if dic:
            for k,v in dic.items():
                new_dic[k]=len(v)

        return new_dic


    #******# 仅为日历显示
    def split_all_tasks(self,all_task_info_list):
        """
         拆分所有任务，该方法会将所有任务根据其日期长度拆分为每一天的任务。比如：一个任务起始日期为2020-11-20，结束日期为
         2020-11-21，工时为4，那么就拆分为两个任务，分别为2020-11-20与2020-11-21每天的独立任务。
         需要基于已经切分跨日后的日期计算长度与计算工时：count_day 和 count_workhour.
         这里再添加2个变量，一个是one_date即切分后的一天长日期，一个 one_date_workhour 即该任务在该天的均分时长
        :param all_task_info_list:
        :return:
        """
        all_splited_tasks=[]
        for task in all_task_info_list:
            start_date=task['cut_start_date']  #以截取过后的起始日期作为计算起始日期
            end_date=task['cut_end_date']      #以截取过后的结束日期作为计算结束日期
            count_day=task['count_day']        #以截取过后的日期长度作为计算日期长度
            count_workhour=task['count_workhour'] #以截取过后的工时作为计算工时
            if count_day ==1:  #当日期长度为1时，表示起始与结束日期是同一天
                task['one_date']=start_date
                task['one_date_workhour']=count_workhour
                all_splited_tasks.append(task)
            else: #当起始与结束日期不是同一天时
                date_list=self.getAlldaysInStartEndDay(start_date,end_date)
                count_workhour /= len(date_list)
                for d in date_list:
                    new_task=copy.deepcopy(task)
                    new_task['one_date']=d
                    new_task['one_date_workhour'] = count_workhour

                    all_splited_tasks.append(new_task)

        return all_splited_tasks


    def get_project_every_task(self,date,task_list):
        """
        传入指定日期与任务列表，查询所有涉及该日期的任务
        比如输入2020-11-20 起始日期与结束日期范围有该日期的都列出来,
        一般传入的是某个项目里的所有任务，所以得到的结果不是该部门一天的，注意！
        这个是当点击具体日历时才显示对于的具体任务
        :param
        :param task_list: [task1,task2,...],
        :return:  [task1_has_date,task1_has_date,...]
        """
        in_date_tasks=[]
        if task_list:
            for task in task_list:
                start_date=task['start_date']
                end_date=task['end_date']
                if self.is_date_in_start_end(date,start_date,end_date): #当在该日期内
                    in_date_tasks.append(task)

        return in_date_tasks



    def delta_date(self,start_date,end_date):
        """
        计算起始与结束日期长度,当结果>0 表示日期顺，<0 表示日期反
        :param start_date:
        :param end_date:
        :return:
        """
        start = datetime.datetime.strptime(start_date, "%Y-%m-%d")
        end = datetime.datetime.strptime(end_date, "%Y-%m-%d")
        delta_day = (end - start).days
        return delta_day

    def getAlldaysInStartEndDay(self,start_date,end_date):
        """
        给定任意2个日期返回其中之间的所有日期
        :param start_date:
        :param end_date:
        :return:
        """
        date_list = []
        begin_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
        end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
        while begin_date <= end_date:
            date_str = begin_date.strftime("%Y-%m-%d")
            date_list.append(date_str)
            begin_date += datetime.timedelta(days=1)
        return date_list

    def is_date_in_start_end(self,date,start_date,end_date):
        """
        传入指定日期date，判定该日期是否在 start_date,end_date之间
        :param date:
        :param start_date:
        :param end_date:
        :return:
        """
        s_detal = self.delta_date(start_date, date) #正序大于等于0
        e_detal=self.delta_date(date,end_date) #正序大于等于0
        if s_detal>=0 and e_detal>=0:
            return True
        else:
            return False


    def _get_project_cname_ename(self,project_name,project_map):
        """
        通过project_name获取项目中文名称
        :param project_name:
        :param project_map: project_map={'proj_gjwd': ['古剑网大', 'GJWD'],'xxx'"..}
        :return:
        """
        c_name='未知项目'
        e_name='UNKOWN'
        if '(' in project_name:
            c_name = project_name.split('(')[0]
            e_name = project_name.split('(')[1][:-1]
        else:
            names=project_map.values()
            for i in names:
                if project_name ==i[1]:
                    c_name=i[0]
                    e_name=i[1]

        return c_name,e_name



    ##每日数据统计


    def get_department_artists(self,department):
        """
        根据传入department，返回该部门的所以人员列表
        :return:
        """
        department_artists=[]
        time_connect, time_cursor = self._init_timeaxis_db()
        select_sql="select artist from timeaxis.r_account where conf_department_id=" \
                   "(select cg_id from timeaxis.r_department where name='{}')".format(department)
        try:
            time_cursor.execute(select_sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    department_artists.append(i[0])
        except Exception as get_department_artists_ERR:
            print('get_department_artists_ERR:',str(get_department_artists_ERR))
        finally:
            time_connect.close()
        return department_artists


    def get_department_scheduler_assinged_data(self, department_artists, start_date, end_date):
        """
        直接获取该部门所有人员的当前排期系统里读取到的任务，均分日期后的任务,同时格式为department_ori_data: {'蔡宝润': [['2020-10-19', None, None], ['2020-10-20', None, None], ['2020-10-21', None, None]]}
department_data: {'蔡宝润': [['2020-10-19', '10.0(0)'], ['2020-10-20', '12.0(0)'], ['2020-10-21', '12.0(0)']]}
        :param department_artists:
        :param start_date:
        :param end_date:
        :return:  返回的是对比过排期预分配的任务
                department_ori_data ：{'artist1':[[date, self.list_to_dict(assigned_tasks), {}]],列表形式，[['2020-11-11', {'0': {'id': '', 'task_type': 'cgtw',...},{}],['2020-11-12',{},{}],...],
                                       'artist2':[[],[],...]}

                每天的任务形式
        """
        data_list = self.mz_data.getAlldaysInStartEndDay(start_date, end_date)
        department_ori_data = {}
        department_assigned_data = {}
        if department_artists:
            # print('department_artists:',department_artists)
            for artist in department_artists:
                assigned_tasks = self.get_alltask_by_range_date(artist, start_date,
                                                                       end_date)  # 获取该人员指定日期里的所有任务列表,切拆分为每天任务
                for date in data_list:
                    assigned_workhour = self.extra_assigned_workhour(date, assigned_tasks)
                    show_hour = '{}_0'.format(round(assigned_workhour, 2))

                    per_info = [date, show_hour]
                    if artist not in department_assigned_data.keys():
                        department_assigned_data[artist] = [per_info]
                    else:
                        department_assigned_data[artist].append(per_info)
                    if artist not in department_ori_data.keys():
                        department_ori_data[artist] = [[date, self.list_to_dict(assigned_tasks,date), {}]]
                    else:
                        department_ori_data[artist].append([date, self.list_to_dict(assigned_tasks,date), {}])

        return department_ori_data, department_assigned_data

    """
    get_department_data 才是最终函数
    """
    # def get_department_data(self,department,start_date,end_date):
    #     """
    #     从note表中获取本地存储的所有该部门的数据
    #     :return:
    #     """
    #     department_artists=self.get_department_artists(department)
    #     # department_ori_data=  {}   #从note表里查询的原始数据
    #     try:
    #         department_ori_data,department_data=self.get_department_scheduler_assinged_data(department_artists,start_date,end_date)
    #         # for k,v in department_ori_data.items():
    #         #     print(k,len(v),v)
    #     except Exception as get_department_scheduler_assinged_data_ERR:
    #         print('get_department_scheduler_assinged_data_ERR:',str(get_department_scheduler_assinged_data_ERR))
    #     #department_data={}   #从note查询到的数据过滤后的工时数据
    #     time_connect, time_cursor = self._init_taskprogress_db()
    #     select_sql="select artist,date,assigned_tasks,submit_tasks from note where department='{}' and date>='{}' and date<='{}'" \
    #                "  ".format(department,start_date,end_date)#self.start_date '2020-10-01'
    #     search_data={}
    #
    #     try:
    #         time_cursor.execute(select_sql)
    #         result=time_cursor.fetchall()
    #         if result:   #这是查询本地有手动填写的人员信息
    #             for i in result:
    #                 artist=i[0]
    #                 date=i[1]
    #                 assigned_tasks=i[2]
    #                 submit_tasks=i[3]
    #                 # print('get_department_data 002')
    #                 if artist not in search_data.keys():
    #                     search_data[artist]= department_ori_data[artist]       #self.get_alltask_by_date(artist, date)  #只循环一次，获取当前人员的该日排期数据前后15天所有数据
    #                 # print('type(assigned_tasks):',type(assigned_tasks))
    #                 # print('get_department_data 003')
    #                 assigned_workhour=self.extra_assigned_workhour(date,assigned_tasks) if assigned_tasks else self.get_the_date_workhour(search_data[artist],date)#这里查询的仅为note已填写的当前已分配数据
    #                 # print(artist,date,assigned_workhour)
    #                 #当但如果当日没有提交的话，就用现在排期软件读取到的排期
    #                 #进行统计，然后查询本地记录的安排任务，如果本地存在那么就替换掉即时查询的排期上的安排任务。
    #                 # print('get_department_data 003.1')
    #                 submit_workhour=self.extra_submit_workhour(submit_tasks)
    #                 # print('get_department_data 003.2')
    #
    #                 show_hour='{}_{}'.format(round(assigned_workhour,2),round(submit_workhour,2))
    #                 per_info=[date,show_hour]
    #                 #直接更新
    #                 # print('get_department_data 004')
    #                 scheduler_assined_data=department_data[artist]
    #                 for g in range(len(scheduler_assined_data)):
    #                     if date==scheduler_assined_data[g][0]:  #当原查询日常里有该日期时,替换掉
    #                         scheduler_assined_data[g]=per_info  #字典可以直接替换值，不动keys
    #                 # print('get_department_data 005')
    #     except Exception as get_department_data_ERR:
    #         print('get_department_data_ERR:',str(get_department_data_ERR))
    #     finally:
    #         time_connect.close()
    #
    #     return department_ori_data,department_data

    def get_department_data(self, department, start_date, end_date):
        """
        还是要从daily里去取，因为note存储的只是当天的任务填写，daily是可以后面补填的
        :param department:
        :param start_date:
        :param end_date:
        :return:
        """
        department_artists=self.get_department_artists(department)
        # department_ori_data=  {}   #从note表里查询的原始数据
        try:
            department_ori_data,department_data=self.get_department_scheduler_assinged_data(department_artists,start_date,end_date)
            # for k,v in department_ori_data.items():
            #     print(k,len(v),v)
        except Exception as get_department_scheduler_assinged_data_ERR:
            print('get_department_scheduler_assinged_data_ERR:',str(get_department_scheduler_assinged_data_ERR))
        #department_data={}   #从note查询到的数据过滤后的工时数据
        time_connect, time_cursor = self._init_taskprogress_db()
        # select_sql="select artist,date,assigned_tasks,submit_tasks from note where department='{}' and date>='{}' and date<='{}'" \
        #            "  ".format(department,start_date,end_date)#self.start_date '2020-10-01'
        select_sql="select artist,date,show_workhour,worktime from daily where department='{}' and date>='{}' and date<='{}'".format(department,start_date,end_date)

        search_data={}

        try:
            time_cursor.execute(select_sql)
            result=time_cursor.fetchall()
            if result:   #这是查询本地有手动填写的人员信息
                artist_date_allhour={} #{'artist1' : {'2020-11-20':'10_8','2020-11-22':'12_4',...},
                                       # 'artist2' : {'2020-11-20':'4_4','2020-11-25':'11_6',...}}
                artist_date_task_list_map={} #{'artist1' : {'2020-11-20':[(show_workhour,worktime),(show_workhour,worktime),...],
                                                           #'2020-11-22':[(show_workhour,worktime),(show_workhour,worktime),...],
                #                              'artist2' : {'2020-11-20':[(show_workhour,worktime),(show_workhour,worktime),...],
                for i in result:
                    artist=i[0]
                    date=i[1]
                    show_workhour=i[2]
                    worktime=i[3]

                    #assigned_workhour= show_workhour #float(show_workhour) if show_workhour else self.get_the_date_workhour(search_data[artist],date)

                    #submit_workhour = worktime   #self.extra_submit_workhour(submit_tasks)
                    if artist not in artist_date_task_list_map.keys():
                        artist_date_task_list_map[artist]={date:[(show_workhour,worktime)]}
                    else:
                        value=artist_date_task_list_map[artist]
                        if date not in value.keys():
                            artist_date_task_list_map[artist][date]=[(show_workhour,worktime)]
                        else:
                            artist_date_task_list_map[artist][date].append((show_workhour, worktime))
                    #show_hour='{}_{}'.format(round(assigned_workhour,2),round(submit_workhour,2))
                    #per_info=[date,show_hour]
                #计算artist_date_allhour
                if artist_date_task_list_map:
                    for a,v in artist_date_task_list_map.items():
                        the_artist=a

                        date_hour_map={}  # {'2020-11-20':'10_8','2020-11-22':'12_4',...}
                        for d,sw_list in v.items():   #v = {'2020-11-20':[(show_workhour,worktime),(show_workhour,worktime),...]
                            the_date=d
                            all_s=0
                            all_w=0
                            for sw in sw_list:
                                s=sw[0]
                                w=sw[1]
                                all_s+=float(s)
                                all_w+=float(w)
                            show_hour = '{}_{}'.format(round(all_s, 2), round(all_w, 2))
                            # if the_date not in date_hour_map.keys():
                            date_hour_map[the_date]=show_hour

                        # if the_artist not in artist_date_allhour.keys():
                        artist_date_allhour[the_artist]=date_hour_map
                        # else:
                        #     artist_date_allhour[the_artist]
                if artist_date_allhour:#{'张谷雷': {'2020-11-16': '12.0_8.0', '2020-11-20': '0.0_9.0', '2020-11-19': '2.0_5.0'}, '黄锐': {...},...}
                    print('artist_date_allhour:',artist_date_allhour)
                    for ar,sh in artist_date_allhour.items():
                    #直接更新 ar:'张谷雷'
                        #   sh :{'2020-11-16': '12.0_8.0', '2020-11-20': '0.0_9.0', '2020-11-19': '2.0_5.0'},
                        scheduler_assined_data=department_data[ar] #[['2020-11-16', '8.33_0'], ['2020-11-17', '8.33_0'],
                        for g in range(len(scheduler_assined_data)):
                            p_date=scheduler_assined_data[g][0]
                            if p_date in sh: #当原查询日常里有该日期时,替换掉
                                scheduler_assined_data[g][1]=sh[p_date]  #字典可以直接替换值，不动keys

        except Exception as get_department_data_ERR:
            print('get_department_data_ERR:',str(get_department_data_ERR))
        finally:
            time_connect.close()

        return department_ori_data,department_data



    def get_the_date_workhour(self,all_data_list,date):
        """
        获取指定天里的所有任务的总工时,传入的是指定日期内的排期所有任务数据
        :param all_data_list:  [['2020-11-28', {'0': {'id': '', 'task_ty,...},{}],[2020-11-29,{},{}]]]
        :return:
        """
        # print('get_the_date_workhour:',all_data_list[0])
        all_workhour=0
        if all_data_list:
            for per_data_list in all_data_list:
                # if data['date']==date:
                if per_data_list[0]==date and per_data_list[1]:
                    for v in per_data_list[1].values():

                        show_workhour = v['show_workhour']
                        all_workhour += float(show_workhour)
        return all_workhour


    def extra_assigned_workhour(self,date,tasks):
        """
        传入当日分配的任务字典，提取当日所有分配任务总时长
        这里需要注意的是，当该日为空时，需要去排期软件上查询那一天的安排
        :param artist: 该制作人员
        :param date:  该日期
        :param tasks:
        :return:
        """
        # print('extra_assigned_workhour:',tasks)
        all_workhour=0
        if tasks:
            if isinstance(tasks,dict):  #当为字典时，表示是从本地note获取的
                for k,v in tasks.items():
                    show_workhour=v['show_workhour']
                    all_workhour+=float(show_workhour)
            elif isinstance(tasks,list):
                for i in tasks:
                    if i['date']==date:
                        # print('iii:',i)
                        show_workhour=i['show_workhour']
                        all_workhour+=float(show_workhour)
        return all_workhour

    def extra_submit_workhour(self,tasks):
        """
        传入当日提交的任务字典，提取当日所有提交任务总时长。

        :param tasks:
        :return:
        """
        # print('task type:',type(tasks))
        all_workhour=0
        if tasks:
            for k,v in tasks.items():
                show_workhour=v['worktime']
                all_workhour+=float(show_workhour)
        return all_workhour


    def list_to_dict(self,task_list,date):
        """
        将任务列表转成字典形式
        :param task_list: [{'id': '', 'task_type': 'cgtw', 'start_date':xx,...},{...},..]
        :param   date :给定日期
        :return: 返回当日的所有任务 ['0':{...},'1':{...},'2':{...},...]
        """
        data={}
        if task_list:
            # print('list_to_dict:',task_list)
            the_date_task_lists=[]
            for task_data in task_list:
                if task_data['date']==date:
                    the_date_task_lists.append(task_data)
            if the_date_task_lists:
                for i in range(len(the_date_task_lists)):
                    data[str(i)]=task_list[i]

        return data

    #***#
    def get_alltask_by_range_date(self,artist,start_date,end_date):
        """
        根据所给定日期范围,获取排期系统上task表与custom_task表里对应的任务数据
        :param artist:  艺术家姓名
        :param date:
        :return: data_list  返回拆分过日期的所以任务列表
        """
        data_list = []
        try:
            #获取cgtw类型任务,获取范围时间内(含跨日期)的任务
            sche_connect, sche_cursor=self._init_timeaxis_db()
            task_sql="select task_type,start_date,end_date,status,show_name,show_workhour,workhour,project_shortname," \
                 "department,project_color,id,client_status_retake_count,project_name,assigner from timeaxis.task where artist='{0}' and start_date <= '{1}' and end_date >='{2}'".format(artist,end_date,start_date)

            sche_cursor.execute(task_sql)
            result=sche_cursor.fetchall()
            if result:
                for i in result:
                    # print(i)
                    data={}
                    data['id']=''  #初始化任务,默认id为空,表示非已分配daily表中任务
                    data['task_type']=i[0]
                    data['start_date']=i[1]
                    data['end_date'] = i[2]
                    data['status']=i[3]
                    data['show_name']=i[4]
                    data['show_workhour'] = i[5]
                    data['workhour']=i[6]
                    data['project_shortname']=i[7]
                    data['department'] = i[8]
                    data['project_color']=i[9]
                    data['color'] = i[9]  #取通用字段名color
                    data['r_id']=i[10]
                    data['client_status_retake_count'] = i[11]  #客户返修任务用来分开记录工时录入
                    data['full_name']=i[12]  #task表里的project_name是 中文名(简称)
                    data['assigner']=i[13]
                    data['artist']= artist
                    data['worktime']='0'  #初始化任务信息将worktime字段加进去
                    data['client_worktime'] = '0'  # 初始化任务信息将client_worktime字段加进去
                    data['note']=''   #初始化将任务备注字段加进去
                    data['date']=''    #self.date  #初始化加入当前date
                    data['assigned_score']='5'  #初始化安排合理度
                    data['workhour_score']='5'  #初始化工时合理度
                    data['pass_task']=0  #初始化是否为忽略任务
                    if data['start_date']==data['end_date']:  #非跨天任务
                        data['date']=data['start_date']
                        data_list.append(data)
                    else:   #跨天任务，拆成每个任务
                        task_date_list=self.mz_data.getAlldaysInStartEndDay(data['start_date'],data['end_date'])
                        data['show_workhour']=str(float( data['show_workhour'])/len(task_date_list))  #将每个任务按长度均分
                        for d in task_date_list:
                            sperate_data={}
                            for k,v in data.items():
                                sperate_data[k]=v   #复制data
                            sperate_data['date'] = d
                            data_list.append(sperate_data)

                    # data_list.append(data)
            # print('get_alltask_by_range_date 002')
            #获取自定义任务  过滤掉超时任务
            custom_sql="select start_date,end_date,show_name,show_workhour,project_name,department,color,id,workhour,assigner from " \
                       "timeaxis.custom_task where artist='{0}'and start_date <= '{1}' and end_date >='{2}' ".format(artist,end_date,start_date) # and custom_type !='超时'   超时认为不能过滤

            sche_cursor.execute(custom_sql)
            # print('get_alltask_by_range_date 002.1')
            c_result=sche_cursor.fetchall()
            # print('get_alltask_by_range_date 002.3')
            if c_result:
                for c in c_result:
                    # print('get_alltask_by_range_date 000',c)
                    data={}
                    data['id'] = ''  # 初始化任务,默认id为空,表示非已分配daily表中任务
                    data['task_type'] = 'custom'
                    data['start_date']=c[0]
                    data['end_date']=c[1]
                    data['show_name']=c[2]
                    data['show_workhour']=c[3]
                    # print('get_alltask_by_range_date 001.1',c[4])
                    data['project_shortname']=self.get_project_shotname(c[4])
                    # print('get_alltask_by_range_date 001.2', data['project_shortname'])
                    data['full_name'] = ''#初始化任务信息将project_name
                    data['department']=c[5]
                    data['color']=c[6]
                    # print('get_alltask_by_range_date 00c1')
                    data['r_id']=c[7]
                    data['workhour']=c[8]
                    data['assigner']=c[9]
                    data['client_status_retake_count']=0  #自定义任务默认客户返修次数为0
                    data['status']=''  #通用状态字段添加
                    data['artist']= artist
                    data['worktime']='0'  #初始化任务信息将worktime字段加进去
                    data['client_worktime'] = '0'  # 初始化任务信息将client_worktime字段加进去
                    data['note']=''   #初始化将任务备注字段加进去
                    # print('get_alltask_by_range_date 00c2')
                    data['date']=''#self.date  #初始化加入当前date
                    data['assigned_score'] = '5'  # 初始化安排合理度
                    data['workhour_score'] = '5'  # 初始化工时合理度
                    data['pass_task']= 0   #初始化是否为忽略任务
                    # data_list.append(data)
                    # print('get_alltask_by_range_date 003')
                    if data['start_date']==data['end_date']:  #非跨天任务
                        data['date'] = data['start_date']
                        data_list.append(data)
                    else:   #跨天任务，拆成每个任务
                        task_date_list=self.mz_data.getAlldaysInStartEndDay(data['start_date'],data['end_date'])
                        data['show_workhour'] = str(float(data['show_workhour']) / len(task_date_list))
                        for p in task_date_list:
                            # print('get_alltask_by_range_date 005',p)
                            sperate_data={}
                            for k,v in data.items():
                                sperate_data[k]=v   #复制data
                            # print('get_alltask_by_range_date 006')
                            sperate_data['date'] = p
                            sperate_data['start_date']=p  #将起始日期变成该天
                            sperate_data['end_date'] = p   #将结束日期变成该天
                            data_list.append(sperate_data)
                            # print('get_alltask_by_range_date 007')
                        # print('get_alltask_by_range_date 007')
            # print('get_alltask_by_range_date 003')
        except Exception as get_task_by_date_ERR:
            print('get_task_by_date_ERR:',str(get_task_by_date_ERR))

        finally:
            sche_connect.close()

        return data_list

    def get_project_shotname(self,name):
        shotname=''
        if name:
            if '(' not in name:
                shotname=name
            else:
                shotname=name.split('(')[1][:-1]
        return shotname


    # def close_all(self):
    #     """
    #     关闭所有连接
    #     :return:
    #     """
    #     # self.cgtw_connect.close()
    #     self.time_connect.close()

if __name__ == '__main__':

    handler=DB_handler()
    # all_task_info_list=handler.select_task_in_dates('2020-11-20','2020-11-23')
    # print(len(all_task_info_list))
    # print(handler.project_map)
    # print(all_task_info_list)

    # all_splited_tasks=handler.split_all_tasks(all_task_info_list)
    #
    # department_per_project_map=handler.get_all_department_per_project_map(all_task_info_list)
    # all_nodes,project_linkes,artist_linkes=handler.get_sankey_data(department_per_project_map)
    # print(all_nodes)
    # for i in artist_linkes:
    #     print(i)

    # project_name_workhour_map,artist_count_map=handler.get_pie_data(all_task_info_list)
    # print(project_name_workhour_map)
    # print(artist_count_map)

    # department_per_project_map=handler.get_all_department_per_project_map(all_splited_tasks)
    #
    # print(department_per_project_map.keys())
    # print(department_per_project_map['05场景'].keys())
    # # for i in department_per_project_map['05场景'].values():
    # #     print(len(i))
    # task_list=department_per_project_map['07特效一']['proj_by']
    # the_date_tasks=handler.get_project_every_task('2020-11-21',task_list)
    # # print(len(the_date_tasks),the_date_tasks)
    # for i in the_date_tasks:
    #     print(i)
    #

#每日获取
    # start_date='2020-11-25'
    # end_date='2020-11-27'
    # department='01美术'
    # artists=handler.get_department_artists(department)
    # print(artists)
    # # department_ori_data, department_assigned_data=handler.get_department_scheduler_assinged_data(artists,start_date,end_date)
    # department_ori_data, department_assigned_data=handler.get_department_data(department,start_date,end_date)
    #
    # print(department_ori_data)
    # print(department_assigned_data)
    #
    #
    # # print(all_splited_tasks)

#各部门人员map
    department_artists_map=handler.department_artists_map
    print(department_artists_map)

#各部门安排总工时以及人员
    all_task_info_list=handler.select_task_in_dates('2020-11-20','2020-11-23')
    department_per_project_map = handler.get_all_department_per_project_map(all_task_info_list)
    all_department_all_workhour, all_department_use_artists = handler.get_department_all_workhour_artists(
        department_per_project_map)
    print(all_department_all_workhour)
    print(all_department_use_artists)
    print(type(all_department_use_artists['08合成一']))








